package com.prinxchengshan.reportbase.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.prinxchengshan.reportbase.Utils.DateUtil;
import com.prinxchengshan.reportbase.Utils.EmailUtil;
import com.prinxchengshan.reportbase.Utils.ExcelUtil;
import com.prinxchengshan.reportbase.Utils.JCoDestinationBuilder;
import com.prinxchengshan.reportbase.mapper.CreditExceedingMapper;
import com.prinxchengshan.reportbase.mapper.CrmInMapper;
import com.prinxchengshan.reportbase.mapper.CrmOutMapper;
import com.prinxchengshan.reportbase.pojo.*;
import com.sap.conn.jco.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import javax.mail.internet.InternetAddress;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
public class ReportService {

    @Autowired(required = false)
    private JCoDestination destination;//获取SAP的链接地址

//    @Autowired
//    private SapCustomerCredit sapCustomerCredit; // 从SAP获取的行数据

    @Autowired
    private List<SapCustomerCredit> sapCustomerCreditList; //从SAP中获取的列表数据

    @Autowired(required = false)
    private CrmInMapper crmInMapper;//从crm中获取信用数据

    @Autowired(required = false)
    private CrmOutMapper crmOutMapper;//向crm中导入数据

    @Autowired
    private NewCreditinput newCreditinput;//crm信用行数据

    private List<NewCreditinput> newCreditinputList;//crm信用数据列表

    @Autowired
    private NewCrmsapreportbase2 newCrmsapreportbase2;//待插入数据的行数据

    @Autowired
    private CreditExceedingMapper ceMapper;  //超额度数据mapper


    @Autowired
    private NewCreditInputHkService newCreditInputHkService;

    private List<NewCrmsapreportbase2> newCrmsapreportbase2List = new ArrayList<NewCrmsapreportbase2>();//带插入数据的list集合

    //从crm中获取的数据的map集合（流水号作为主键）
    private Map<String, NewCreditinput> creditinputMap;

    //设置标志位，标志是不是第一次调用
    private boolean flag = false;


   //上次调用日期
   private String lastDate;

   //上次调用时间
   private String lastTime;

    DateUtil dateUtil = new DateUtil();

    @Autowired
    private EmailUtil emailUtil;

//    每晚将之前所有的差异数据发送给邮箱（最好不要和另外一个定时器冲突了）
//     @Scheduled(cron = "0 5 8,13 * * ?")
//     @Scheduled(cron = "0 51 15 * * ?")
    public void  sendDailyEmail() throws IOException {
        //      查询三个差异金额不为0的行数据
        QueryWrapper<NewCrmsapreportbase2> wrapper = new QueryWrapper<>();
        wrapper.notBetween("differnece_total",-1,1).or().notBetween("difference_amount",-1,1);
        List<NewCrmsapreportbase2> noZeros = crmOutMapper.selectList(wrapper);
        System.out.println("send email...");
        sendEmailAll();
    }

//    每隔一个小时执行一次
    @Scheduled(cron = "0 0 0/1 * * ?")
    // @Scheduled(cron = "0 17 14  * * ?")
    public   void ImportFromSAP() throws JCoException, ParseException, IOException {
        System.out.println("开始执行任务");
        //使用工厂模式构建构建器构建链接地址
        JCoDestination destination = JCoDestinationBuilder.build();
        JCoFunction function = destination.getRepository().getFunction("ZFII034");
        //设置导入参数，没有可以不写

        if (flag == false){
            // function.getImportParameterList().setValue("ZDATE","00000000");
            function.getImportParameterList().setValue("ZDATE","20231108");
            function.getImportParameterList().setValue("ZTIME","000000");
            function.getImportParameterList().setValue("ZTYPE", "1");
            flag = true;
        }else{
            function.getImportParameterList().setValue("ZDATE",lastDate);
            function.getImportParameterList().setValue("ZTIME",lastTime);
            function.getImportParameterList().setValue("ZTYPE", "1");
        }

        lastDate = dateUtil.getToday();
        lastTime = dateUtil.getTimeStr();


        try {
            System.out.println(function);
            JCoContext.begin(destination);
            function.execute(destination);
            System.out.println(function);
            //logger.warn(function.toXML());
        } catch (JCoException e) {
            e.printStackTrace();
        }

        //获取导出参数内表
        JCoTable exp_tab = function.getExportParameterList().getTable("EXP_TAB");


        //将字符串转换为XML的形式
        String toXML = exp_tab.toXML();
        //将xml中的数据添加到list集合中去，方便等下导入数据
        getXmlAttribute(toXML);

        //获取crm数据库里面的视图
        QueryWrapper<NewCreditinput> wrapper = new QueryWrapper<>();
        wrapper.isNotNull("new_name").
                orderByAsc("new_name").
                eq("new_state","10").
                eq("statecode","0");



        List<NewCreditinput> newCreditinputs = crmInMapper.selectList(wrapper);
        ArrayList<NewCreditinput> distinctLists = newCreditinputs.stream()
                .collect(Collectors.collectingAndThen
                        (Collectors.toCollection(() ->
                                        new TreeSet<>(Comparator.comparing(t -> t.getNew_name()))),
                                ArrayList::new
                        )
                );
        //将获取的list集合转换为map集合
        creditinputMap = distinctLists.stream().collect(Collectors.toMap(NewCreditinput::getNew_name, Function.identity()));
        //System.out.println(creditinputMap);
        BigDecimal temp;
        BigDecimal zero = new BigDecimal(0);
        Date date = new Date();
        //清空表数据
//        crmOutMapper.delete(null);
//        每日差异的列表
        List<NewCrmsapreportbase2> noZeros = new ArrayList<NewCrmsapreportbase2>();
        for (SapCustomerCredit sapCustomerCredit : sapCustomerCreditList) {
            newCreditinput = creditinputMap.get(sapCustomerCredit.getZhklsh());
            if (sapCustomerCredit.getZhklsh() == null){
                continue;
            }
            //加入非空判断
            if (newCreditinput == null){
                continue;
            }
            if ("58250713".equals(sapCustomerCredit.getKunnr())){
                continue;
            }

            newCrmsapreportbase2.setNew_name(sapCustomerCredit.getZhklsh());//还款流水号
            newCrmsapreportbase2.setNew_accountcode(sapCustomerCredit.getKunnr());//客户号
            newCrmsapreportbase2.setNew_accountidname(sapCustomerCredit.getName1());//客户名称
            newCrmsapreportbase2.setNew_credittypeid(sapCustomerCredit.getZxylx());//信用类型
            newCrmsapreportbase2.setSap_totalamount(sapCustomerCredit.getZdbje());//SAP总金额
            temp = newCreditinput.getNew_totalamount() == null ? zero : newCreditinput.getNew_totalamount();
            newCrmsapreportbase2.setNew_totalamount(
                  temp
            );//CRM总金额
            newCrmsapreportbase2.setDiffernece_total(
                    newCrmsapreportbase2.getSap_totalamount().subtract(newCrmsapreportbase2.getNew_totalamount())
            );//总金额差异
            newCrmsapreportbase2.setSap_usedamount(sapCustomerCredit.getZhkje());//SAP已用金额
            temp = newCreditinput.getNew_usedamount() == null ? zero : newCreditinput.getNew_usedamount();
            newCrmsapreportbase2.setNew_usedamount(temp);//CRM已用金额
            newCrmsapreportbase2.setDifference_used(
                    newCrmsapreportbase2.getSap_usedamount().subtract(newCrmsapreportbase2.getNew_usedamount())
            );//已用金额差异
            newCrmsapreportbase2.setSap_amount(sapCustomerCredit.getZfhwh());//SAP余额
            temp = newCreditinput.getNew_amount() == null ? zero : newCreditinput.getNew_amount();
            newCrmsapreportbase2.setNew_amount(temp);//CRM余额
            newCrmsapreportbase2.setDifference_amount(
                    newCrmsapreportbase2.getSap_amount().subtract(newCrmsapreportbase2.getNew_amount())
            );//余额差异
//            余额或者总额不为0就会发送邮件
            newCrmsapreportbase2.setInsert_time(lastTime);
            newCrmsapreportbase2.setInsert_date(lastDate);
//           在reportbase2的数据根据流水号查找数据
            QueryWrapper<NewCrmsapreportbase2> wrapper1 = new QueryWrapper<>();
            wrapper1.eq("new_name",newCrmsapreportbase2.getNew_name());
            Long count = crmOutMapper.selectCount(wrapper1);
            if (count == 0){
//               如果没有这个还款流水号的数据，则进行删除
                crmOutMapper.insert(newCrmsapreportbase2);
            }else {
//                如果存在这条还款流水号，就会根据还款流水号更新数据
                crmOutMapper.updateById(newCrmsapreportbase2);
            }

        }

        newCreditInputHkService.ImportFromSAP();

//       将此次同步数据中余额差异和总金额差异不为0的数据发送邮件
        System.out.println("send email...");
        sendEmail(lastDate,lastTime);

    }



    /**
     * dom4j解析Xml
     * @param xml
     */
    public void getXmlAttribute(String xml) throws ParseException {
        Document doc = null;
        try {
            // 将字符串转为XML
            doc = DocumentHelper.parseText(xml);
            // 获取根节点
            Element rootElt = doc.getRootElement();
            List<Element> items = rootElt.elements();
//           这是一个全局变量，清空一下数据，免得有历史数据遗留
            sapCustomerCreditList.clear();
            for (Element recordEle : items) {
                //获取item节点


                SapCustomerCredit sapCustomerCredit = new SapCustomerCredit();

                //BUKRS
                    Iterator BUKRS = recordEle.elementIterator("BUKRS");
                    while (BUKRS.hasNext()) {
                        Element abkRecord = (Element) BUKRS.next();
                        //获取ABK节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setBukrs(text);
                    }

                    //ZXYZH
                    Iterator ZXYZH = recordEle.elementIterator("ZXYZH");
                    while (ZXYZH.hasNext()) {
                        Element abkRecord = (Element) ZXYZH.next();
                        //获取ZXYZH节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZxyzh(text);
                    }

                    //获取ZHKLSH节点
                    Iterator ZHKLSH = recordEle.elementIterator("ZHKLSH");
                    while (ZHKLSH.hasNext()) {
                        Element abkRecord = (Element) ZHKLSH.next();
                        //获取ZHKLSH节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZhklsh(text);
                    }

                    //ZHKDH
                    Iterator ZHKDH = recordEle.elementIterator("ZHKDH");
                    while (ZHKLSH.hasNext()) {
                        Element abkRecord = (Element) ZHKDH.next();
                        //获取ZHKDH节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZhkdh(text);
                    }

                    //KUNNR
                    Iterator KUNNR = recordEle.elementIterator("KUNNR");
                    while (KUNNR.hasNext()) {
                        Element abkRecord = (Element) KUNNR.next();
                        //获取KUNNR节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setKunnr(text);
                    }

                    //BU_SORT2
                    Iterator BU_SORT2 = recordEle.elementIterator("BU_SORT2");
                    while (KUNNR.hasNext()) {
                        Element abkRecord = (Element) BU_SORT2.next();
                        //获取KUNNR节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setBu_sort2(text);
                    }

                    //NAME1
                    Iterator NAME1 = recordEle.elementIterator("NAME1");
                    while (NAME1.hasNext()) {
                        Element abkRecord = (Element) NAME1.next();
                        //获取name1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setName1(text);
                    }
                    //ZXYLX
                    Iterator ZXYLX = recordEle.elementIterator("ZXYLX");
                    while (ZXYLX.hasNext()) {
                        Element abkRecord = (Element) ZXYLX.next();
                        //获取ZXYLX节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZxylx(text);
                    }
                    //XYNAME
                    Iterator XYNAME = recordEle.elementIterator("XYNAME");
                    while (XYNAME.hasNext()) {
                        Element abkRecord = (Element) XYNAME.next();
                        //获取XYNAME节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setXyname(text);
                    }
                    //ZNAME1
                    Iterator ZNAME1 = recordEle.elementIterator("ZNAME1");
                    while (ZNAME1.hasNext()) {
                        Element abkRecord = (Element) ZNAME1.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZname1(text);
                    }
                    //ZDBJE
                    Iterator ZDBJE = recordEle.elementIterator("ZDBJE");
                    while (ZDBJE.hasNext()) {
                        Element abkRecord = (Element) ZDBJE.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZdbje(BigDecimal.valueOf(Double.parseDouble(text)));
                    }
                    //ZFHJE
                    Iterator ZFHJE = recordEle.elementIterator("ZFHJE");
                    while (ZFHJE.hasNext()) {
                        Element abkRecord = (Element) ZFHJE.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZfhje(BigDecimal.valueOf(Double.parseDouble(text)));
                    }
                    //ZLJJE
                    Iterator ZLJJE = recordEle.elementIterator("ZLJJE");
                    while (ZLJJE.hasNext()) {
                        Element abkRecord = (Element) ZLJJE.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZljje(BigDecimal.valueOf(Double.parseDouble(text)));
                    }
                    //ZFHWH
                    Iterator ZFHWH = recordEle.elementIterator("ZFHWH");
                    while (ZFHWH.hasNext()) {
                        Element abkRecord = (Element) ZFHWH.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZfhwh(BigDecimal.valueOf(Double.parseDouble(text)));
                    }
                    //ZHKJE
                    Iterator ZHKJE = recordEle.elementIterator("ZHKJE");
                    while (ZHKJE.hasNext()) {
                        Element abkRecord = (Element) ZHKJE.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZhkje(BigDecimal.valueOf(Double.parseDouble(text)));
                    }
                    //ZTZJE
                    Iterator ZTZJE = recordEle.elementIterator("ZTZJE");
                    while (ZTZJE.hasNext()) {
                        Element abkRecord = (Element) ZTZJE.next();
                        //获取ZNAME1节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZtzje(BigDecimal.valueOf(Double.parseDouble(text)));
                    }
                    //ZKYZT
                    Iterator ZKYZT = recordEle.elementIterator("ZKYZT");
                    while (ZKYZT.hasNext()) {
                        Element abkRecord = (Element) ZKYZT.next();
                        //获取ZKYZT节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZkyzt(text);
                    }
                    //ZTNAME
                    Iterator ZTNAME = recordEle.elementIterator("ZTNAME");
                    while (ZTNAME.hasNext()) {
                        Element abkRecord = (Element) ZTNAME.next();
                        //获取ZTNAME节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZtname(text);
                    }
                    //ZXYZQSR
                    Iterator ZXYZQSR = recordEle.elementIterator("ZXYZQSR");
                    while (ZXYZQSR.hasNext()) {
                        Element abkRecord = (Element) ZXYZQSR.next();
                        //获取ZXYZQSR节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        SimpleDateFormat formatter = new SimpleDateFormat( "yyyyMMdd");
                        Date date = formatter.parse(text);
                        sapCustomerCredit.setZxyzqsr(date);
                    }
                    //ZXYZDQI
                    Iterator ZXYZDQI = recordEle.elementIterator("ZXYZDQI");
                    while (ZXYZDQI.hasNext()) {
                        Element abkRecord = (Element) ZXYZDQI.next();
                        //获取ZXYZDQI节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        SimpleDateFormat formatter = new SimpleDateFormat( "yyyyMMdd");
                        Date date = formatter.parse(text);
                        sapCustomerCredit.setZxyzdqi(date);
                    }
                    //ZYHLSH
                    Iterator ZYHLSH = recordEle.elementIterator("ZYHLSH");
                    while (ZYHLSH.hasNext()) {
                        Element abkRecord = (Element) ZYHLSH.next();
                        //获取ZYHLSH节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setZyhlsh(text);
                    }
                    //PURPOSE
                    Iterator PURPOSE = recordEle.elementIterator("PURPOSE");
                    while (PURPOSE.hasNext()) {
                        Element abkRecord = (Element) PURPOSE.next();
                        //获取PURPOSE节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setPurpose(text);
                    }
                    //CHANGED_AT
                    Iterator CHANGED_AT = recordEle.elementIterator("CHANGED_AT");
                    while (CHANGED_AT.hasNext()) {
                        Element abkRecord = (Element) CHANGED_AT.next();
                        //获取CHANGED_AT节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        SimpleDateFormat formatter = new SimpleDateFormat( "yyyyMMdd");
                        Date date = formatter.parse(text);
                        sapCustomerCredit.setChanged_at(date);
                    }
                    //URID
                    Iterator URID = recordEle.elementIterator("URID");
                    while (URID.hasNext()) {
                        Element abkRecord = (Element) URID.next();
                        //获取URID节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setUrid(text);
                    }
                    //DBURID
                    Iterator DBURID = recordEle.elementIterator("DBURID");
                    while (DBURID.hasNext()) {
                        Element abkRecord = (Element) DBURID.next();
                        //获取DBURID节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setDburid(text);
                    }
                    //DYURID
                    Iterator DYURID = recordEle.elementIterator("DYURID");
                    while (DYURID.hasNext()) {
                        Element abkRecord = (Element) DYURID.next();
                        //获取DYURID节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        sapCustomerCredit.setDyurid(text);
                    }
                    //ZGXRQ
                    Iterator ZGXRQ = recordEle.elementIterator("ZGXRQ");
                    while (ZGXRQ.hasNext()) {
                        Element abkRecord = (Element) ZGXRQ.next();
                        //获取ZGXRQ节点下的所有节点
                        Iterator f = abkRecord.elementIterator();
                        String text = abkRecord.getText();
                        SimpleDateFormat formatter = new SimpleDateFormat( "yyyyMMdd");
                        Date date = formatter.parse(text);
                        sapCustomerCredit.setZgxrq(date);
                    }
                    sapCustomerCreditList.add(sapCustomerCredit);
//                }
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }
    }


    /**
     * 使用邮件发送excel附件
     */
    public void sendEmail(String lastDate,String lastTime) throws IOException, JCoException, ParseException {

        // List<NewCrmsapreportbase2> noZeros = getNoZeros(lastDate, lastTime);
        List<NewCrmsapreportbase2> noZeros = getNoZeros();
        // 将回款数据导入到数据库中（SAP和CRM的对比数据）
        // newCreditInputHkService.ImportFromSAP();
        // List<NewCrmSapHk> noZerosHk = newCreditInputHkService.getNoZeros(lastDate,lastTime);
        List<NewCrmSapHk> noZerosHk = newCreditInputHkService.getNoZeros();

        if (noZeros.size() == 0 && noZerosHk.size() == 0){
            return;
        }

        updateNoZeros(noZeros);
        newCreditInputHkService.updateNoZeros(noZerosHk);

        // noZeros = getNoZeros(lastDate, lastTime);
        noZeros = getNoZeros();
        // noZerosHk = newCreditInputHkService.getNoZeros(lastDate,lastTime);
        noZerosHk = newCreditInputHkService.getNoZeros();

        if (noZeros.size() == 0 && noZerosHk.size() == 0){
            return;
        }


        XSSFWorkbook xssfWorkbook = null;
        // 将回款差异和还款差异数据写到excel中
        if(noZeros.size() > 0){
            // 回款有差异添加到excel中
            xssfWorkbook = getXssf(noZeros);
        }
        if (noZerosHk.size() > 0){
            // 还款有差异添加到excel中
            xssfWorkbook = newCreditInputHkService.writeExcel(noZerosHk,xssfWorkbook);
        }


        ExcelUtil.writeExcel(xssfWorkbook,"SAP-CRM每日信用差异.xlsx");

        // 测试单个附件邮件发送
        String title = "SAP-CRM每日信用差异数据";
        String content = "SAP-CRM每日信用差异数据";
        File file = new File("src\\main\\resources\\SAP-CRM每日信用差异.xlsx");
        // emailUtil.sendMessageCarryFile(to, title, content, file);
        // String[] toStrings = new String[]{
        //         "zlzhou@prinxchengshan.com",
                // "chyxu@prinxchengshan.com",
                // "xfsui@prinxchengshan.com",
                // "lichen@prinxchengshan.com",
                // "dyqi@prinxchengshan.com",
                // "hyta@prinxchengshan.com"
        // };
        String[] toStrings = ExcelUtil.ExcelToArray("信用邮件发送名单.xlsx");
        InternetAddress[] toList = emailUtil.setReceiver(toStrings);
        emailUtil.sendEmail(toList ,title , content , file);

    }

    /**
     * 使用邮件发送excel附件
     */
    public void sendEmailAll() throws IOException {

        List<NewCrmsapreportbase2> noZeros = getNoZeros();
        List<NewCrmSapHk> noZerosHk = newCreditInputHkService.getNoZeros();

        if (noZeros.size() == 0 && noZerosHk.size() == 0){
            return;
        }

        updateNoZeros(noZeros);
        newCreditInputHkService.updateNoZeros(noZerosHk);

        noZeros = getNoZeros();
        noZerosHk = newCreditInputHkService.getNoZeros();

        if (noZeros.size() == 0 && noZerosHk.size() == 0){
            return;
        }

        // 生成要发送的excel文件
        XSSFWorkbook xssfWorkbook = null;
        if (noZeros.size()>0){
            xssfWorkbook = getXssf(noZeros);
        }
        if (noZerosHk.size()>0){
            xssfWorkbook = newCreditInputHkService.writeExcel(noZerosHk,xssfWorkbook);
        }


        ExcelUtil.writeExcel(xssfWorkbook,"SAP-CRM每日信用差异.xlsx");

        // 测试单个附件邮件发送
        String title = "SAP-CRM每日信用差异数据";
        String content = "SAP-CRM每日信用差异数据";
        File file = new File("src\\main\\resources\\SAP-CRM每日信用差异.xlsx");
        // emailUtil.sendMessageCarryFile(to, title, content, file);

        String[] toStrings = ExcelUtil.ExcelToArray("信用邮件发送名单.xlsx");
        // String[] toStrings = new String[]{
        //         "zlzhou@prinxchengshan.com",
        //         // "chyxu@prinxchengshan.com",
        //         // "xfsui@prinxchengshan.com",
        //         // "lichen@prinxchengshan.com",
        //         // "dyqi@prinxchengshan.com",
        //         // "hyta@prinxchengshan.com"
        // };
        InternetAddress[] toList = emailUtil.setReceiver(toStrings);
        emailUtil.sendEmail(toList ,title , content , file);

    }


    /**
     * 生成excel表格
     * @param noZeros
     * @throws IOException
     */
    public XSSFWorkbook getXssf(List<NewCrmsapreportbase2> noZeros) throws IOException {
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook();
        XSSFSheet xssfSheet=xssfWorkbook.createSheet("SAP-CRM每日信用差异_回款");
        //注意，这种新增sheet的方式，如果你的excel原来在第一个sheet页面上有内容，会自动覆盖原来的内容,所以尽量使用空的EXCEL文档
        String[] biaoti=new String[]{"还款流水号","客户号","客户名称","SAP总金额","CRM总金额","总金额差异",
                "SAP已用金额","CRM已用金额","已用金额差异","SAP余额","CRM余额","余额差异"};
        Row row=xssfSheet.createRow(0);
        for (int i=0;i< biaoti.length;i++){
            row.createCell(i).setCellValue(biaoti[i]);
        }

        int rowIndex = 1;
        for (NewCrmsapreportbase2 noZero : noZeros) {
            int colunIndex = 0;
            XSSFRow row1 = xssfSheet.createRow(rowIndex++);
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_name());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_accountcode());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_accountidname());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_totalamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_totalamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDiffernece_total().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_usedamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_usedamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDifference_used().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_amount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_amount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDifference_amount().doubleValue());
        }
        // //
        // String[] biaoti2=new String[]{"客户号","信用用超金额"};
        // XSSFSheet xssfSheet2=xssfWorkbook.createSheet("CRM用超信用额度");
        // Row row2=xssfSheet2.createRow(0);
        // for (int i=0;i< biaoti2.length;i++){
        //     row2.createCell(i).setCellValue(biaoti2[i]);
        // }
        //
        // List<CreditExceeding> creditExceedings = ceMapper.callProcedure();
        // int rowIndex2 = 1;
        // for (CreditExceeding creditExceeding : creditExceedings) {
        //     int colunIndex = 0;
        //     XSSFRow row1 = xssfSheet2.createRow(rowIndex2++);
        //     // 用户号
        //     row1.createCell(colunIndex++).setCellValue(creditExceeding.getNewName());
        //     // 用超金额
        //     row1.createCell(colunIndex++).setCellValue(creditExceeding.getNewAvailable().doubleValue());
        // }
        // //


        // String local_path = System.getProperty("user.dir");
        // //获取当前用户的当前工作目录
        // String path = local_path+"\\src\\main\\resources\\SAP-CRM每日信用差异.xlsx";
        // //添加上文件的路径，组成文件的绝对路径
        // File file=new File(path);
        // FileOutputStream fileOutputStream=new FileOutputStream(file);
        // xssfWorkbook.write(fileOutputStream);
        // //通过流的方式写入到文件中中
        // fileOutputStream.close();
        // //使用完成之后要记得关闭这个流

        return xssfWorkbook;
    }


    public List<NewCrmsapreportbase2> getNoZeros(String lastDate,String lastTime){
        //      查询三个差异金额不为0的行数据
        QueryWrapper<NewCrmsapreportbase2> wrapper = new QueryWrapper();

        String tempTime = "";
        String tempDate = "";

        if (lastTime.equals("000000")){
            tempTime = "230000";
            tempDate = Integer.parseInt(lastDate)-1+"";
        }else {
            tempTime = Integer.parseInt(lastTime)-10000+"";
            tempDate = lastDate;
        }

        wrapper.eq("insert_time",tempTime).
                eq("insert_date",tempDate)
//                .and(i->i.ne("differnece_total",0).or().ne("difference_amount",0));
                .and(i->i.notBetween("differnece_total",-1,1).or().notBetween("difference_amount",-1,1));
        List<NewCrmsapreportbase2> noZeros = crmOutMapper.selectList(wrapper);
        // 遍历noZeros,删除还款流水号zk开头的差异金额在-1和1的行
        Iterator<NewCrmsapreportbase2> iterator = noZeros.iterator();
        while (iterator.hasNext()) {
            NewCrmsapreportbase2 next = iterator.next();
            if ( (next.getDifference_amount().compareTo(new BigDecimal(-1)) != -1)
                    && (next.getDifference_amount().compareTo(new BigDecimal(1)) != 1)
                    && next.getNew_name().startsWith("ZK")
            )   {
                iterator.remove();
            }
        }
        return noZeros;
    }

    public List<NewCrmsapreportbase2> getNoZeros(){
        //      查询三个差异金额不为0的行数据
        QueryWrapper<NewCrmsapreportbase2> wrapper = new QueryWrapper();
        //wrapper.
//                ne("differnece_total",0).or().ne("difference_amount",0);
        // 还款流水号的差异金额在-1和1之间的不发邮件
        wrapper.
                notBetween("differnece_total",-1,1).or().
                notBetween("difference_amount",-1,1);

        List<NewCrmsapreportbase2> noZeros = crmOutMapper.selectList(wrapper);


        // 遍历删除zk开头的差异金额不在-1和1的行
        Iterator<NewCrmsapreportbase2> iterator = noZeros.iterator();
        while (iterator.hasNext()) {
            NewCrmsapreportbase2 next = iterator.next();
            if (next.getNew_name().startsWith("ZK") && (next.getDifference_amount().compareTo(new BigDecimal(-1)) != -1)
                    && (next.getDifference_amount().compareTo(new BigDecimal(1)) != 1))
            {
                // 删除元素
                iterator.remove();
            }
        }
        return noZeros;
    }


    public void  updateNoZeros( List<NewCrmsapreportbase2> noZeros ){
        // 对于SAP重推给CRM的数据，发送之前要重新校验一下数据是否正确
        for (NewCrmsapreportbase2 noZero : noZeros) {
            //获取crm数据库里面的视图
            QueryWrapper<NewCreditinput> updateWrapper = new QueryWrapper<>();
            updateWrapper.isNotNull("new_name").
                    eq("new_name",noZero.getNew_name()).
                    eq("new_state","10").
                    eq("statecode","0");

            NewCreditinput newCreditinput = crmInMapper.selectOne(updateWrapper);
            if (newCreditinput == null){
                continue;
            }
            noZero.setNew_totalamount(newCreditinput.getNew_totalamount());
            noZero.setNew_usedamount(newCreditinput.getNew_usedamount());
            noZero.setNew_amount(newCreditinput.getNew_amount());
            noZero.setDiffernece_total(
                    noZero.getSap_totalamount().subtract(noZero.getNew_totalamount())
            );//总金额差异
            noZero.setDifference_used(
                    noZero.getSap_usedamount().subtract(noZero.getNew_usedamount())
            );//已用金额差异
            noZero.setDifference_amount(
                    noZero.getSap_amount().subtract(noZero.getNew_amount())
            );//余额差异

            QueryWrapper<NewCrmsapreportbase2> wrapper = new QueryWrapper<>();
            // 通过流水号更新
            wrapper.eq("new_name",noZero.getNew_name());
            // 更新数据
            crmOutMapper.update(noZero,wrapper);

        }

    }

}
